use DB_AH

DECLARE @WHSECODE NVARCHAR(50)
DECLARE @ITEMCODE NVARCHAR(50)
DECLARE @PC_DOCUMENT NVARCHAR(50)
DECLARE @MINDATE DATETIME
DECLARE @ASOFDATE DATETIME

SET @ITEMCODE = 'AH111-0001'
SET @WHSECODE = '01'
SET @PC_DOCUMENT = 'PC135'
set @ASOFDATE = '2013-08-19 18:58:57.290'
DROP TABLE #BEGINNING, #PURCHASES  , #PURCHASE_RETURN, #DELIVERIES, #SALES_RETURN, #GOODISSUE, #GOODRECEIPT, #WAREHOUSE_TRANSFER, #REPORT

USE DB_ARYSTA
GO

CREATE PROCEDURE SP_STOCKMOVEMENT 
	@PC_DOCUMENT NVARCHAR(50), @WHSECODE NVARCHAR(50), @ITEMCODE NVARCHAR(50), @ASOFDATE DATETIME
	AS
	BEGIN

	DECLARE @MINDATE DATETIME

	
		SET @ASOFDATE = CAST(CAST(MONTH(@ASOFDATE) AS NVARCHAR) + '/' +
					CAST(DAY(@ASOFDATE) AS NVARCHAR) + '/' +
					CAST(YEAR(@ASOFDATE) AS NVARCHAR) + ' 23:59:59:000' AS DATETIME)



SELECT 'Beginning Inventory' Document, ophc.DocNum, PostingDate, WhseCode, WhseName, Remarks, ItemCode, ItemName, UoM, UnitCount, LotNumber, ExpiryDate, PHC1.UnitCost INTO #BEGINNING FROM OPHC JOIN PHC1 ON OPHC.DocNum = PHC1.DocNum 
WHERE ItemCode = @ITEMCODE AND OPHC.WhseCode = @WHSECODE AND OPHC.DocNum = @PC_DOCUMENT
--PHYSICAL COUNT

SELECT @MINDATE = #BEGINNING.PostingDate FROM #BEGINNING

--select @MINDATE
-- Purchases (Receiving Report/Purchase Return)
SELECT 'Purchases' Document, opdn.DocNum, SupplierInvoice, PostingDate, Reference, Location WhseCode, (SELECT WAREHOUSE.Dscription FROM WAREHOUSE WHERE WAREHOUSE.Code = OPDN.Location) as WhseName, CardCode, CardName, Remarks, pdn1.ItemCode, pdn1.ItemDesc, pdn1.Qty, PDN1.UnitPrice UnitCost INTO #PURCHASES   FROM OPDN JOIN PDN1 ON OPDN.DOCNUM = PDN1.DOCNUM
WHERE ItemCode = @ITEMCODE AND Location = @WHSECODE AND PostingDate BETWEEN @MINDATE AND @ASOFDATE

SELECT 'Purchase Return' Document, OGRN.DocNum, OGRN.PostingDate, OGRN.Location WhseCode, (SELECT WAREHOUSE.Dscription FROM WAREHOUSE WHERE WAREHOUSE.Code = OGRN.Location) as WhseName, OGRN.Remarks, GRN1.ItemCode, GRN1.ItemDesc, GRN1.Qty, UnitPrice UnitCost  INTO #PURCHASE_RETURN FROM OGRN JOIN GRN1 ON OGRN.DOCNUM = GRN1.DOCNUM
WHERE ItemCode = @ITEMCODE AND Location = @WHSECODE AND PostingDate BETWEEN @MINDATE AND @ASOFDATE

-- Sales Deliveries (DR/Sales Return)
SELECT 'Deliveries' Document, ODLN.DocNum, ODLN.DocumentCode + ' ' + ODLN.DivReference SIDRNo, ODLN.PostingDate, ODLN.Reference, ODLN.Location WhseCode, (SELECT WAREHOUSE.Dscription FROM WAREHOUSE WHERE WAREHOUSE.Code = ODLN.Location) as WhseName, ODLN.CardCode, ODLN.CardName, Remarks, DLN2.ItemCode, DLN2.ItemName, DLN2.QtySrvd, UnitCost  INTO #DELIVERIES FROM ODLN JOIN DLN2	 ON ODLN.DOCNUM = DLN2.DOCNUM
WHERE ItemCode = @ITEMCODE AND Location = @WHSECODE AND PostingDate BETWEEN @MINDATE AND @ASOFDATE

SELECT 'Sales Return' Document, ORIN.DocNum, ORIN.PostingDate, ORIN.Reference, ORIN.Location WhseCode, (SELECT WAREHOUSE.Dscription FROM WAREHOUSE WHERE WAREHOUSE.Code = ORIN.Location) as WhseName, ORIN.CardCode, ORIN.CardName, Remarks, RIN1.ItemCode, RIN1.ItemDesc, RIN1.Qty, UnitCost INTO #SALES_RETURN  FROM ORIN JOIN RIN1 ON ORIN.DOCNUM = RIN1.DOCNUM
WHERE ItemCode = @ITEMCODE AND Location = @WHSECODE AND PostingDate BETWEEN @MINDATE AND @ASOFDATE

-- Inventories - Goods Receipt/Goods Issue

SELECT 'Inventory - Good Issuance' Document, OIGE.DocNum, OIGE.PostingDate, OIGE.WhsCode, OIGE.WhsName, OIGE.Reference, OIGE.Remarks, IGE1.ItemCode, IGE1.ItemName, IGE1.Qty, UnitCost INTO #GOODISSUE FROM OIGE JOIN IGE1 ON OIGE.DocNum = IGE1.DocNum
WHERE ItemCode = @ITEMCODE AND OIGE.WhsCode = @WHSECODE AND PostingDate BETWEEN @MINDATE AND @ASOFDATE

SELECT 'Inventory - Good Receipt' Document, OIGN.DocNum, OIGN.PostingDate, OIGN.WhsCode, OIGN.WhsName, OIGN.Reference, OIGN.Remarks, IGN1.ItemCode, IGN1.ItemName, IGN1.Qty, UnitCost INTO #GOODRECEIPT FROM OIGN JOIN IGN1 ON OIGN.DocNum = IGN1.DocNum
WHERE ItemCode = @ITEMCODE AND OIGN.WhsCode = @WHSECODE AND PostingDate BETWEEN @MINDATE AND @ASOFDATE

-- Warehouse Transfer - IN

SELECT 'Warehouse Transfer - In' Document, OWT.DocNum, OWT.PostingDate, OWT.DestWhCode, (SELECT WAREHOUSE.Dscription  FROM WAREHOUSE WHERE WAREHOUSE.Code = OWT.DestWhCode) AS WhseName, OWT.Remarks, WT1.ItemCode, WT1.ItemName, WT1.RcvdQty, WT1.UoM, UnitCost INTO #WAREHOUSE_TRANSFER FROM OWT JOIN WT1 ON OWT.DocNum = WT1.DocNum
WHERE ItemCode = @ITEMCODE AND DestWhCode = @WHSECODE AND PostingDate BETWEEN @MINDATE AND @ASOFDATE

SELECT PostingDate, DocNum, Document, ItemCode, ItemName, UnitCount InQty, 0 OutQty, UnitCost INTO #REPORT FROM #BEGINNING 
UNION ALL
SELECT PostingDate, DocNum, Document, ItemCode, ItemDesc, Qty InQty, 0 OutQty, UnitCost  FROM #PURCHASES 
UNION ALL
SELECT PostingDate, DocNum, Document, ItemCode, ItemDesc, 0 InQty, Qty OutQty, UnitCost  FROM #PURCHASE_RETURN
union all
SELECT PostingDate, DocNum, Document, ItemCode, ItemName, 0 InQty, QtySrvd OutQty , UnitCost FROM #DELIVERIES
UNION ALL 
SELECT PostingDate, DocNum, Document, ItemCode, ItemDesc, 0 InQty, Qty OutQty, UnitCost  FROM #SALES_RETURN
union all
SELECT PostingDate, DocNum, Document, ItemCode, ItemName, 0 InQty, Qty OutQty, UnitCost  FROM #GOODISSUE
union all
SELECT PostingDate, DocNum, Document, ItemCode, ItemName, Qty InQty, 0 OutQty, UnitCost  FROM #GOODRECEIPT
union all
SELECT PostingDate, DocNum, Document, ItemCode, ItemName, RcvdQty InQty, 0 OutQty, UnitCost  FROM #WAREHOUSE_TRANSFER 





create table #STOCKMOVEMENT
(
	PostingDate DATETIME,
	DocNum nvarchar(50),
	Document nvarchar(250),
	ItemCode nvarchar(80),
	ItemName nvarchar(150),
	InQty numeric(18,2),
	OutQty numeric(18,2),
	UnitCost numeric(18, 2),
	TotalCost numeric(18,2))


SELECT recno()  as RecNo, *, case when InQty > 0 then InQty * UnitCost else OutQty * UnitCost end as TotalCost FROM #REPORT ORDER BY PostingDate


END